INSERT INTO product_warehouse_stock (product_id, warehouse_id, stock)
SELECT p.product_id,
       (SELECT warehouse_id FROM warehouses WHERE is_default = 1 LIMIT 1) AS warehouse_id,
       p.stock
FROM products p
ON DUPLICATE KEY UPDATE stock = VALUES(stock);



SELECT cid AS customer_id,
       SUM(payments)   AS repayment_count,
       SUM(paid)       AS total_paid,
       SUM(outstanding) AS outstanding_sales_balance
FROM (
    SELECT r.customer_id AS cid, COUNT(*) AS payments, SUM(r.amount) AS paid, 0 AS outstanding
    FROM repayments r
    LEFT JOIN customers c ON c.customer_id = r.customer_id
    WHERE r.type='customer' AND r.customer_id IS NOT NULL AND c.customer_id IS NULL
    GROUP BY r.customer_id
    UNION ALL
    SELECT s.customer_id, 0, 0, SUM(s.balance)
    FROM sales s
    LEFT JOIN customers c ON c.customer_id = s.customer_id
    WHERE s.customer_id > 0 AND c.customer_id IS NULL
    GROUP BY s.customer_id
) t
GROUP BY cid;



INSERT INTO customers (customer_id, name, phone, address, opening_balance, created_at)
SELECT x.cid,
       CONCAT('بیرته جوړ شوی #', x.cid),
       '', '', 0, NOW()
FROM (
    SELECT DISTINCT r.customer_id AS cid
    FROM repayments r
    LEFT JOIN customers c ON c.customer_id = r.customer_id
    WHERE r.type='customer' AND r.customer_id IS NOT NULL AND c.customer_id IS NULL
    UNION
    SELECT DISTINCT s.customer_id
    FROM sales s
    LEFT JOIN customers c ON c.customer_id = s.customer_id
    WHERE s.customer_id > 0 AND c.customer_id IS NULL
) x;